<?php
	include("../connect/connect.php");
	
	$sql = "drop table if exists event_daily;";
	$rs=mysql_query($sql);
	
	$sql="create table event_daily as select t1.event_id,concat('รายละเอียดจากตำรวจ : ',(case when t1.pol_des is null then ' ' else t1.pol_des end),'รายละเอียดจากทหาร : ',(case when t2.commt is null then ' ' else t2.commt end)) as event_des,
pol_typ,type_acc1,type_acc2,type_acc3,pol_cause,mil_cause,point_x,point_y,t1.pol_recid,t2.code_acc from 
(select concat(right(pol_dte,6),'-',pol_plcid,'-',pol_tme) as event_id,pol_des,pol_typ,cause as pol_cause,pol_recid from rev_policeevent where sendto_event <> 1 ) t1
 left join
(select code_acc as event_id,commt,type_acc1,type_acc2,type_acc3,cause as mil_cause,point_x,point_y,code_acc from rev_militaryevent where sendto_event <> 1 ) t2
 on t1.event_id=t2.event_id
union
select code_acc as event_id,commt as event_des,'' as pol_typ,type_acc1,type_acc2,type_acc3,'' as pol_cause,cause as mil_cause,point_x,point_y,'' as pol_revid,code_acc from rev_militaryevent
where sendto_event <> 1 and code_acc not in (select concat(right(pol_dte,6),'-',pol_plcid,'-',pol_tme) as event_id from rev_policeevent where sendto_event <> 1)
";
	$rs=mysql_query($sql);
	echo "<br><br>complete create event_daily";	
	//ปรับปรุง sendto_event ใน rev_policeevent
	$tempY=date("Y");
	if($tempY<"2550"){
		$tempY=(int)$tempY+543;
	}
	$datetime_send= $tempY.date("-m-d-H:i");
	$sql = "update rev_policeevent set sendto_event=1,datetime_sendto_event='$datetime_send'  where sendto_event=0;";
	//echo "<br>".$sql;	
	$rs=mysql_query($sql);	
	if($rs){
		echo "<br>update send rev_policeevent status complete";
	}

	//ปรับปรุง sendto_event ใน rev_militaryevent	
	//$tempY=date("Y");
	//if($tempY<"2550"){
	//	$tempY=(int)$tempY+543;
	//}
	//$datetime_send= $tempY.date("-m-d-H:i");
	$sql = "update rev_militaryevent set sendto_event=1,datetime_sendto_event='$datetime_send'  where sendto_event=0;";
	//echo "<br>".$sql;
	$rs=mysql_query($sql);	
	if($rs){
		echo "<br>update send rev_militaryevent status complete";
	} 
	
	// create victim_daily
	$sql = "drop table if exists victim_daily;";
	$rs=mysql_query($sql);
	
	$sql="create table victim_daily as 
select b.event_id,a.pid,a.prename,a.fname,a.sname,a.sts,'' as residence,'' as sex,'' as religion,'' as occup   from rev_policevictim a inner join event_daily b on a.pol_evnid=b.pol_recid
 where a.sendto_victim <> 1
union
select b.event_id,a.code_id,a.prename,a.fname,a.sname,a.sts,'' as residence,'' as sex,'' as religion,'' as occup  from rev_militaryvictim a inner join event_daily b on a.code_acc=b.code_acc
 where a.sendto_victim <> 1
union
select concat(right(adate,6),'-',vaplace,'-',atime) as event_id,id,prename,name,fname,sts,residence,sex,religion,occup from rev_visadmission_last a inner join event_daily b on concat(right(a.adate,6),'-',a.vaplace,'-',a.atime)=b.event_id
 where a.sendto_victim <> 1
";
	$rs=mysql_query($sql);	
	echo "<br>complete create victim_daily";	
	//ปรับปรุง sendto_victim ใน rev_militaryvictim,rev_policevictim,rev_visadmission_last
	$tempY=date("Y");
	if($tempY<"2550"){
		$tempY=(int)$tempY+543;
	}
	$datetime_send= $tempY.date("-m-d-H:i");
	$sql = "update rev_policevictim set sendto_victim=1,datetime_sendto_victim='$datetime_send'  where sendto_victim=0;";
	$rs=mysql_query($sql);
	//echo "<br>".$sql;
	if($rs){
		echo "<br>update send rev_policevictim status complete";
	} 
	$sql = "update rev_militaryvictim set sendto_victim=1,datetime_sendto_victim='$datetime_send'  where sendto_victim=0;";
	$rs=mysql_query($sql);
	//echo "<br>".$sql;
		if($rs){
		echo "<br>update send rev_militaryvictim status complete";
	} 
	$sql = "update rev_visadmission_last set sendto_victim=1,datetime_sendto_victim='$datetime_send'  where sendto_victim=0;";
	$rs=mysql_query($sql);
	//echo "<br>".$sql;	
	if($rs){
		echo "<br>update send rev_visadmission_last status complete";
	} 
	
	mysql_close($c);
?>